package com.huiyin.db;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.huiyin.utils.Compare;
import com.huiyin.utils.LogUtil;
import com.huiyin.utils.ProCompare;
import com.huiyin.utils.StringUtils;

public class SQLOpearteImpl {
    private static String databasepath = "/data/data/%s/databases";
    private DBOpenHelper mDBOpenHelper;
    private SQLiteDatabase mSQLiteDatabase;
    private Context mContext;

    public SQLOpearteImpl(Context context) {
        this.mContext = context;
        copyData();
        mDBOpenHelper = new DBOpenHelper(context, null);
        mSQLiteDatabase = mDBOpenHelper.getWritableDatabase();
    }

    public void CloseDB() {
        mSQLiteDatabase.close();
    }

    public int checkIdByName(String cityName) {
        int id = -1;
        if (StringUtils.isBlank(cityName))
            return id;
        cityName = cityName.replaceAll("市", "");
        cityName = cityName.replaceAll("省", "");
        cityName = cityName.replaceAll("区", "");
        Cursor c = mSQLiteDatabase.query("area_table", new String[]{"_id", "parentId", "areaName", "level"}, "areaName = ?",
                new String[]{cityName}, null, null, null);
        if (c.moveToFirst()) {
            id = c.getInt(c.getColumnIndex("_id"));
        }
        c.close();
        return id;
    }
    
    //add by zhyao @2015/8/17 添加根据区域名查询区域id
    public int checkIdByAreaName(String areaName) {
    	 int id = -1;
         if (StringUtils.isBlank(areaName))
             return id;
         Cursor c = mSQLiteDatabase.query("area_table", new String[]{"_id", "parentId", "areaName", "level"}, "areaName = ?",
                 new String[]{areaName}, null, null, null);
         if (c.moveToFirst()) {
             id = c.getInt(c.getColumnIndex("_id"));
         }
         c.close();
         return id;
    }


    /**
     * @param address  通过详细地址去查询CityId
     * @return CityId
     */
    public int checkIdByAddress(String address) {
        int id = -1;
        if (StringUtils.isBlank(address))
            return id;

        ArrayList<Area> provinces = checkAllProvince();

        for(Area area :provinces) {
            if(address.contains(area.areaName)) {
                ArrayList<Area> cities = checkAllCityById(area.rowId);
                for (Area city :cities) {
                    if(address.contains(city.areaName)) {
                        id = city.rowId;
                        return id;
//                        ArrayList<Area> districes = checkAllDistriceById(city.rowId);
//                        for (Area distriy : districes) {
//                            if(address.contains(distriy.areaName)) {
//                                id = distriy.rowId;
//                                return id;
//                            }
//                        }
                    }
                }
            }
        }
        return id;
    }

    public String checkIdsByAddress(String address) {
        String ids = "";
        if (StringUtils.isBlank(address))
            return ids;

        ArrayList<Area> provinces = checkAllProvince();
        for(Area area :provinces) {
            if(address.contains(area.areaName)) {
                ids += area.rowId + ",";
                ArrayList<Area> cities = checkAllCityById(area.rowId);
                for (Area city :cities) {
                    if(address.contains(city.areaName)) {
                        ids += city.rowId + ",";
                        ArrayList<Area> districes = checkAllDistriceById(city.rowId);
                        for (Area distriy : districes) {
                            if(address.contains(distriy.areaName)) {
                                ids += distriy.rowId;
                                return ids;
                            }
                        }
                    }
                }
            }
        }

        return ids;
    }

    /**
     * 查询所有的省 包括直辖市和自治区特区
     *
     * @return
     */
    public ArrayList<Area> checkAllProvince() {
        ArrayList<Area> provinces = new ArrayList<Area>();
        Cursor c = mSQLiteDatabase.query("area_table", new String[]{"_id", "parentId", "areaName", "level"}, "level = ?",
                new String[]{"1"}, null, null, null);
        while (c.moveToNext()) {
            Area temp = new Area();
            temp.rowId = c.getInt(c.getColumnIndex("_id"));
            temp.areaName = c.getString(c.getColumnIndex("areaName"));
            temp.parentId = c.getInt(c.getColumnIndex("parentId"));
            temp.level = c.getInt(c.getColumnIndex("level"));
            provinces.add(temp);
        }
        c.close();
    	Comparator comparator = new ProCompare();// 省排序，江苏省排第一
		Collections.sort(provinces, comparator);
        return provinces;
    }

    /**
     * 通过省Id查询下属市
     *
     * @param provinceId
     * @return
     */
    public ArrayList<Area> checkAllCityById(int provinceId) {
        ArrayList<Area> cities = new ArrayList<Area>();
        Cursor c = mSQLiteDatabase.query("area_table", new String[]{"_id", "parentId", "areaName", "level"},
                "level = ? and parentId = ?", new String[]{"2", String.valueOf(provinceId)}, null, null, null);
        while (c.moveToNext()) {
            Area temp = new Area();
            temp.rowId = c.getInt(c.getColumnIndex("_id"));
            temp.areaName = c.getString(c.getColumnIndex("areaName"));
            temp.parentId = c.getInt(c.getColumnIndex("parentId"));
            temp.level = c.getInt(c.getColumnIndex("level"));
            cities.add(temp);
        }
        c.close();
    	Comparator comparator = new Compare();// 市排序，扬州市排第一
		Collections.sort(cities, comparator);
        return cities;
    }
    
	/**
	 * 通过城市Id获取城市名
	 * 
	 * @param cId
	 * @return
	 */
	public String getCityById(int cId) {
		String result;
		Cursor c = mSQLiteDatabase.query("area_table", new String[] { "_id",
				"parentId", "areaName", "level" }, "level = ? and _id = ?",
				new String[] { "2", String.valueOf(cId) }, null, null, null);
		c.moveToFirst();
		result = c.getString(c.getColumnIndex("areaName"));
		c.close();
		return result;
	}

	public int checkProIdByCityName(String city) {

		Cursor c = mSQLiteDatabase.query("area_table", new String[] { "_id",
				"parentId", "areaName", "level" },
				"level = ? and areaName = ? ", new String[] { "2", city },
				null, null, null);
		c.moveToFirst();
		int parentId = c.getInt(c.getColumnIndex("parentId"));
		return parentId;
	}

    /**
     * 通过城市ID查询下属区
     *
     * @param cityId
     * @return
     */
    public ArrayList<Area> checkAllDistriceById(int cityId) {
        ArrayList<Area> districes = new ArrayList<Area>();
        Cursor c = mSQLiteDatabase.query("area_table", new String[]{"_id", "parentId", "areaName", "level"},
                "level = ? and parentId = ?", new String[]{"3", String.valueOf(cityId)}, null, null, null);
        while (c.moveToNext()) {
            Area temp = new Area();
            temp.rowId = c.getInt(c.getColumnIndex("_id"));
            temp.areaName = c.getString(c.getColumnIndex("areaName"));
            temp.parentId = c.getInt(c.getColumnIndex("parentId"));
            temp.level = c.getInt(c.getColumnIndex("level"));
            districes.add(temp);
        }
        c.close();
        return districes;
    }

    public void copyData() {

        if (null == mContext || null == databasepath) {
            return;
        }

        String dpath = String.format(databasepath, mContext.getApplicationInfo().packageName);
        String path = dpath + "/huiyin_db.db";
        LogUtil.e("path", path);
        File file = new File(dpath);
        File fileData = new File(path);
        if (!fileData.exists()) {
            InputStream in = null;
            FileOutputStream out = null;
            try {
                if (!file.exists()) {
                    file.mkdir();
                }
                in = mContext.getAssets().open("huiyin_db.db");
                out = new FileOutputStream(fileData);
                int length = -1;
                byte[] buf = new byte[1024];
                while ((length = in.read(buf)) != -1) {
                    out.write(buf, 0, length);
                }
                out.flush();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e1) {
                        e1.printStackTrace();
                    }
                }
                if (out != null) {
                    try {
                        out.close();
                    } catch (IOException e1) {
                        e1.printStackTrace();
                    }
                }
            }
        }
    }

    public class Area {
        public int rowId;
        public String areaName;
        public int parentId;
        public int level;
        public boolean isSelected;
    }
}
